package com.shop.dao.impl;

import com.alipay.api.domain.GoodsDetail;
import com.shop.dao.ShowDao;
import com.shop.entity.*;
import com.shop.util.ConnectionUtils;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class ShowDaoImpl implements ShowDao {

    //搜索前8
    @Override
    public List<Show> query_Goods_8() {
        Connection con = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<Show> list = new ArrayList<Show>();
        try {
            con = ConnectionUtils.getConnection();

            String sql = "SELECT gd.gd_id, GTT.gtt_name,gd.gd_price,img.img_path\n" +
                    "FROM GOOD_DETAIL gd \n" +
                    "JOIN GOODS gs ON gd.g_id = gs.g_id\n" +
                    "JOIN goods_third_type GTT ON GD.gtt_id = GTT.gtt_id\n" +
                    "JOIN img_path IMG ON GD.gd_id = IMG.gd_id ORDER BY gd.gd_id DESC LIMIT 8";

            pstmt = con.prepareStatement(sql);
            rs = pstmt.executeQuery();
            while(rs.next()) {
                Show show = new Show();
                show.setG_id(rs.getInt("gd_id"));
                show.setG_name(rs.getString("gtt_name"));
                show.setGd_price(rs.getDouble("gd_price"));
                show.setImg_path(rs.getString("img_path"));
                list.add(show);
            }
            return list;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
//        finally {
//            ConnectionUtils.close(rs,pstmt,con);
//        }
        return list;
    }

    @Override
    public List<Show> query_GDS_BySecondType(int secondType,int pageSize,int pageNum) {
        Connection con = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<Show> list = new ArrayList<Show>();
        try {
            con = ConnectionUtils.getConnection();

            String sql = "SELECT gd.gd_id, GTT.gtt_name,gd.gd_price,img.img_path\n" +
                    "FROM GOOD_DETAIL gd \n" +
                    "JOIN GOODS gs ON gd.g_id = gs.g_id\n" +
                    "JOIN goods_third_type GTT ON GTT.gtt_id = GD.gtt_id\n" +
                    "JOIN img_path IMG ON GD.gd_id = IMG.gd_id \n" +
                    "JOIN goods_second_type GST ON GST.gst_id = GD.gst_id\n" +
                    "WHERE gd.GST_id = ? ORDER BY gd.gd_id DESC LIMIT ?,?";

            pstmt = con.prepareStatement(sql);
            pstmt.setInt(1,secondType);
            pstmt.setInt(2,(pageNum-1)*pageSize);
            pstmt.setInt(3,pageSize);
            rs = pstmt.executeQuery();
            while(rs.next()) {
                Show show = new Show();
                show.setG_id(rs.getInt("gd_id"));
                show.setG_name(rs.getString("gtt_name"));
                show.setGd_price(rs.getDouble("gd_price"));
                show.setImg_path(rs.getString("img_path"));
                list.add(show);
            }
            return list;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            ConnectionUtils.close(rs,pstmt,con);
        }
        return list;
    }

    @Override
    public List<Show> query_GDS_ByBuyNum_7() {
        Connection con = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<Show> list = new ArrayList<Show>();
        try {
            con = ConnectionUtils.getConnection();

            String sql = "SELECT gd.gd_id, GTT.gtt_name,gd.gd_price,img.img_path\n" +
                    "FROM GOOD_DETAIL gd \n" +
                    "JOIN GOODS gs ON gd.g_id = gs.g_id\n" +
                    "JOIN goods_third_type GTT ON GTT.gtt_id = GD.gtt_id\n" +
                    "JOIN img_path IMG ON GD.gd_id = IMG.gd_id \n" +
                    "JOIN goods_second_type GST ON GST.gst_id = GD.gst_id\n" +
                    "ORDER BY gd.gd_buyNum DESC LIMIT 7\n";

            pstmt = con.prepareStatement(sql);
            rs = pstmt.executeQuery();
            while(rs.next()) {
                Show show = new Show();
                show.setG_id(rs.getInt("gd_id"));
                show.setG_name(rs.getString("gtt_name"));
                show.setGd_price(rs.getDouble("gd_price"));
                show.setImg_path(rs.getString("img_path"));
                list.add(show);
            }
            return list;
        } catch (SQLException throwables) {
            System.out.println(throwables);
//            throwables.printStackTrace();
        }
//        finally {
//            ConnectionUtils.close(rs,pstmt,con);
//        }
        return list;
    }

    @Override
    public List<Show> query_GDS_ByBuyNum_12(int pageNum, int pageSize) {
        Connection con = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<Show> list = new ArrayList<Show>();
        try {
            con = ConnectionUtils.getConnection();

            String sql = "SELECT gd.gd_id, GTT.gtt_name,gd.gd_price,img.img_path,gd.gd_buyNum\n" +
                    "FROM GOOD_DETAIL gd \n" +
                    "JOIN GOODS gs ON gd.g_id = gs.g_id\n" +
                    "JOIN goods_third_type GTT ON GTT.gtt_id = GD.gtt_id\n" +
                    "JOIN img_path IMG ON GD.gd_id = IMG.gd_id \n" +
                    "JOIN goods_second_type GST ON GST.gst_id = GD.gst_id\n" +
                    "ORDER BY gd.gd_buyNum DESC LIMIT ?,?";

            pstmt = con.prepareStatement(sql);
            pstmt.setInt(1,(pageNum-1)*pageSize);
            pstmt.setInt(2,pageSize);
            rs = pstmt.executeQuery();
            while(rs.next()) {
                Show show = new Show();
                show.setG_id(rs.getInt("gd_id"));
                show.setG_name(rs.getString("gtt_name"));
                show.setGd_price(rs.getDouble("gd_price"));
                show.setImg_path(rs.getString("img_path"));
                list.add(show);
            }
            return list;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            ConnectionUtils.close(rs,pstmt,con);
        }
        return list;
    }

    @Override
    public List<GoodsContent> queryGoodContent(int gd_id) {
        Connection con = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<GoodsContent> list = new ArrayList<>();
        try {
            con = ConnectionUtils.getConnection();
            String sql = "SELECT GD.gd_id,GD.gd_price,STO.s_id,STO.s_name,GS.g_data,GS.g_datail,GS.g_name,img.img_path\n" +
                    "FROM good_detail GD\n" +
                    "JOIN store STO ON GD.s_id = STO.s_id\n" +
                    "JOIN goods GS\tON GS.g_id = GD.G_ID\n" +
                    "JOIN img_path img ON GD.gd_id = img.gd_id\n" +
                    "WHERE GD.gd_id = ? \n";
            pstmt = con.prepareStatement(sql);
            pstmt.setInt(1,gd_id);
            rs = pstmt.executeQuery();
            while(rs.next()){
                GoodsContent gc = new GoodsContent();
                gc.setGd_id(gd_id);
                gc.setPrice(rs.getDouble("gd_price"));
                gc.setS_id(rs.getInt("s_id"));
                gc.setGd_img(rs.getString("img_path"));
                gc.setS_name(rs.getString("s_name"));
                gc.setG_data(rs.getString("g_data"));
                gc.setG_datail(rs.getString("g_datail"));
                gc.setG_name(rs.getString("g_name"));
                list.add(gc);
            }
            return list;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            ConnectionUtils.close(rs,pstmt,con);
        }
        return list;
    }

    @Override
    public List<GoodsFourType> queryFourTypeByGDId(int g_id) {
        Connection con = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<GoodsFourType> list = new ArrayList<>();
        try {
            con = ConnectionUtils.getConnection();
            String sql = "SELECT GD.gfour_id,GFOUR.gfour_name\n" +
                    "FROM GOOD_DETAIL GD\n" +
                    "JOIN goods_first_type GFT ON GD.gft_id = GFT.gft_id\n" +
                    "JOIN goods_second_type GST ON GFT.gft_id = GST.gft_id\n" +
                    "JOIN goods_third_type GTT ON GST.gst_id = GTT.gst_id\n" +
                    "JOIN goods_four_type GFOUR ON GTT.gtt_id = GFOUR.gtt_id\n" +
                    "JOIN GOODS GS ON GS.g_id = GD.g_id\n" +
                    "WHERE GS.g_id = ?\n" +
                    "GROUP BY GD.gfour_id";
            pstmt = con.prepareStatement(sql);
            pstmt.setInt(1,g_id);
            rs = pstmt.executeQuery();
            while(rs.next()){
                GoodsFourType gft = new GoodsFourType();
                gft.setGfour_id(rs.getInt("gfour_id"));
                gft.setGfour_name(rs.getString("gfour_name"));
                list.add(gft);
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return list;
    }

    @Override
    public int getShoppingCartNum(int u_id) {
        Connection con = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        int num =0;
        try {
            con = ConnectionUtils.getConnection();

            String sql = "SELECT count(SC.u_id) sum \n" +
                    "FROM shoppingcart SC\n" +
                    "JOIN shopcart_detail SD\n" +
                    "ON SC.sc_id = SD.sc_id\n" +
                    "WHERE SC.u_id = ?\n";

            pstmt = con.prepareStatement(sql);
            pstmt.setInt(1,u_id);
            rs = pstmt.executeQuery();
            if(rs.next()) {
                num = rs.getInt("sum");
            }
            return num;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            ConnectionUtils.close(rs,pstmt,con);
        }
        return num;
    }

    @Override
    public List<GoodsTypeVO> getOtherGDType(int gd_id) {
        Connection con = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<GoodsTypeVO> list = new ArrayList<GoodsTypeVO>();
        try {
            con = ConnectionUtils.getConnection();

            String sql = "SELECT GD1.gd_id,GD1.gfour_id,GFOUR.gfour_name\n" +
                    "FROM good_detail GD1\n" +
                    "JOIN goods_four_type GFOUR\n" +
                    "ON GFOUR.gfour_id = GD1.gfour_id\n" +
                    "WHERE(GD1.gft_id,GD1.gst_id,GD1.gtt_id)=\n" +
                    "(\n" +
                    "SELECT GD2.gft_id,GD2.gst_id,GD2.gtt_id\n" +
                    "FROM good_detail GD2\n" +
                    "WHERE GD2.gd_id = ?\n" +
                    ")";

            pstmt = con.prepareStatement(sql);
            pstmt.setInt(1,gd_id);
            rs = pstmt.executeQuery();
            while(rs.next()) {
                GoodsTypeVO detail = new GoodsTypeVO();
                detail.setGd_id(rs.getInt("gd_id"));
                detail.setGfour_name(rs.getString("gfour_name"));
                detail.setGfour_id(rs.getInt("gfour_id"));
                list.add(detail);
            }
            return list;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            ConnectionUtils.close(rs,pstmt,con);
        }
        return list;
    }

    @Override
    public int getSidByUid(int u_id) {
        Connection con = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        int s_id = -1;
        try {
            con = ConnectionUtils.getConnection();

            String sql = "SELECT s_id FROM store WHERE u_id = ?";

            pstmt = con.prepareStatement(sql);
            pstmt.setInt(1,u_id);
            rs = pstmt.executeQuery();
            if(rs.next()) {
                s_id = rs.getInt("s_id");
            }
            return s_id;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            ConnectionUtils.close(rs,pstmt,con);
        }
        return s_id;
    }

    @Override
    public int getGoodsSid(int gd_id) {
        Connection con = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        int s_id = -1;
        try {
            con = ConnectionUtils.getConnection();

            String sql = "SELECT s_id FROM good_detail WHERE gd_id = ?";

            pstmt = con.prepareStatement(sql);
            pstmt.setInt(1,gd_id);
            rs = pstmt.executeQuery();
            if(rs.next()) {
                s_id = rs.getInt("s_id");
            }
            return s_id;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            ConnectionUtils.close(rs,pstmt,con);
        }
        return s_id;
    }

    @Override
    public int getSc_id(int u_id) {
        Connection con = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        int sc_id = -1;
        try {
            con = ConnectionUtils.getConnection();

            String sql = "SELECT sc_id FROM shoppingcart WHERE U_ID = ?";

            pstmt = con.prepareStatement(sql);
            pstmt.setInt(1,u_id);
            rs = pstmt.executeQuery();
            if(rs.next()) {
                sc_id = rs.getInt("sc_id");
            }
            return sc_id;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            ConnectionUtils.close(rs,pstmt,con);
        }
        return sc_id;
    }


    //根据一级分类id查询
    @Override
    public List<Show> query_GDS_ByFirstType_4(int FirstType) {
        Connection con = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<Show> list = new ArrayList<Show>();
        try {
            con = ConnectionUtils.getConnection();

            String sql = "SELECT gd.gd_id, GTT.gtt_name,gd.gd_price,img.img_path\n" +
                    "FROM GOOD_DETAIL gd JOIN GOODS gs ON gd.g_id = gs.g_id\n" +
                    "JOIN goods_third_type GTT ON GTT.gtt_id = GD.gtt_id\n" +
                    "JOIN img_path IMG ON GD.gd_id = IMG.gd_id \n" +
                    "WHERE gd.gft_id = ? ORDER BY gd.gd_id DESC LIMIT 4";

            pstmt = con.prepareStatement(sql);
            pstmt.setInt(1,FirstType);
            rs = pstmt.executeQuery();
            while(rs.next()) {
                Show show = new Show();
                show.setG_id(rs.getInt("gd_id"));
                show.setG_name(rs.getString("gtt_name"));
                show.setGd_price(rs.getDouble("gd_price"));
                show.setImg_path(rs.getString("img_path"));
                list.add(show);
            }
            return list;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            ConnectionUtils.close(rs,pstmt,con);
        }
        return list;
    }

    @Override
    public List<Show> query_GDS_ByFirstType_12(int FirstType, int pageSize, int pageNum) {
        Connection con = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<Show> list = new ArrayList<Show>();
        try {
            con = ConnectionUtils.getConnection();

            String sql = "SELECT gd.gd_id, GTT.gtt_name,gd.gd_price,img.img_path\n" +
                    "FROM GOOD_DETAIL gd \n" +
                    "JOIN GOODS gs ON gd.g_id = gs.g_id\n" +
                    "JOIN goods_third_type GTT ON GTT.gtt_id = GD.gtt_id\n" +
                    "JOIN img_path IMG ON GD.gd_id = IMG.gd_id \n" +
                    "WHERE gd.gft_id = ? \n" +
                    "ORDER BY gd.gd_id DESC \n" +
                    "LIMIT ?,?";

            pstmt = con.prepareStatement(sql);
            pstmt.setInt(1,FirstType);
            pstmt.setInt(2,(pageNum-1)*pageSize);
            pstmt.setInt(3,pageSize);
            rs = pstmt.executeQuery();
            while(rs.next()) {
                Show show = new Show();
                show.setG_id(rs.getInt("gd_id"));
                show.setG_name(rs.getString("gtt_name"));
                show.setGd_price(rs.getDouble("gd_price"));
                show.setImg_path(rs.getString("img_path"));
                list.add(show);
            }
            return list;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            ConnectionUtils.close(rs,pstmt,con);
        }
        return list;
    }

    //
    @Override
    public List<Show> query_GDS_ByGDName(String gdName,int pageSize,int pageNum) {
        Connection con = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<Show> list = new ArrayList<Show>();
        try {
            con = ConnectionUtils.getConnection();

            String sql = "SELECT gd.gd_id, GTT.gtt_name,gd.gd_price,img.img_path\n" +
                    "FROM GOOD_DETAIL gd \n" +
                    "JOIN GOODS gs ON gd.g_id = gs.g_id \n" +
                    "JOIN img_path IMG ON GD.gd_id = IMG.gd_id  \n" +
                    "JOIN GOODS_FIRST_TYPE GFT ON GD.gft_id = GFT.gft_id\n" +
                    "JOIN goods_second_type GST ON GD.gst_id = GST.gst_id\n" +
                    "JOIN goods_third_type GTT ON GD.gtt_id = GTT.gtt_id\n" +
                    "JOIN goods_four_type GFOUR ON GD.gfour_id = GFOUR.gfour_id\n" +
                    "JOIN store sto ON gd.s_id = sto.s_id\n" +
                    "WHERE GS.g_name like ? \n" +
                    "OR GFT.gft_name like ? \n" +
                    "OR GST.gst_name like ?  \n" +
                    "OR GTT.gtt_name like ? \n" +
                    "OR GFOUR.gfour_name like ? \n" +
                    "OR sto.s_name like ? \n" +
                    "ORDER BY gd.gd_id DESC  \n" +
                    "LIMIT ?,?";

            pstmt = con.prepareStatement(sql);
            pstmt.setString(1,"%"+gdName+"%");
            pstmt.setString(2,"%"+gdName+"%");
            pstmt.setString(3,"%"+gdName+"%");
            pstmt.setString(4,"%"+gdName+"%");
            pstmt.setString(5,"%"+gdName+"%");
            pstmt.setString(6,"%"+gdName+"%");
            pstmt.setInt(7,(pageNum-1)*pageSize);
            pstmt.setInt(8,pageSize);

            rs = pstmt.executeQuery();
            while(rs.next()) {
                Show show = new Show();
                show.setG_id(rs.getInt("gd_id"));
                show.setG_name(rs.getString("gtt_name"));
                show.setGd_price(rs.getDouble("gd_price"));
                show.setImg_path(rs.getString("img_path"));
                list.add(show);
            }
            return list;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            ConnectionUtils.close(rs,pstmt,con);
        }
        return list;
    }




}
